﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.Common;
using System.IO;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Checksums;
using System.Xml;
using System.Globalization;
using ResourceBlender_Express;
using ICSharpCode.SharpZipLib.Core;
using Yogesh.ExcelXml;
using System.Data;

/// <summary>
/// Generates resources and outputs to various formats
/// </summary>
public static class ResourceGenerator
{
    /// <summary>
    /// Creates Dictionaries to store bundles from the database.
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The language codes to include.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>True</c> if a summary of the generated resources should be included.</param>
    /// <returns>A KeyValuePair containing <summary (or null if not needed), Dictionary<bundlename, Dictionary<language-code, Dictionary<element, resource>>>></returns>
    public static KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> GenerateBundles(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, resource>>>
        List<string> errors = new List<string>();
        StringBuilder summary = null;
        if(includeSummary) {
            summary = new StringBuilder().AppendFormat("Resources generated by ResourceBlender v{0} on {1}\n\n",Constants.GetVersion().ToString(3),DateTime.Now.ToShortDateString());
        }

        Dictionary<string, int> completionMap = new Dictionary<string, int>();
        int totalStrings = 0;
        Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>();
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = new StringBuilder("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix)
                                    .Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true)).ToString();
            cmd.AddParameterWithValue("@applicationId", applicationId);
            cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for the default language
                string bundleName = reader["bundlename"].ToString(); // id of the bundle
                string elementName = reader["elementname"].ToString(); // eg: element
                if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this form name
                    defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                }
                defaultLanguageStrings[bundleName][elementName] = reader["translation"].ToString();
                totalStrings++;
            }
            reader.Close();
            if(includeSummary) {
                summary.AppendFormat("Application '{0}' contains {1} element{2} in {3} bundle{4}:\n", applicationName, totalStrings, totalStrings == 1 ? "" : "s", defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? "" : "s");
                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                    summary.AppendFormat("*{0} ({1} element{2})\n", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? "" : "s");
                }
                summary.AppendFormat("\nOmitting languages less than {0}% complete\n", threshold);
            }

            cmd.CommandText = new StringBuilder("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang").Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true)).ToString();
            reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string abbrev = reader["language"].ToString();
                string bundle = reader["bundle"].ToString(); // id of the bundle
                string bundleName = reader["bundlename"].ToString(); // eg: bundle1
                string elementName = reader["elementname"].ToString(); // eg: element
                if(includedBundles.Contains(bundle)) {
                    if(!completionMap.ContainsKey(abbrev)) {
                        completionMap[abbrev] = 0;
                    }
                    completionMap[abbrev]++;
                    if(!bundles.ContainsKey(bundleName)) { //  first element in this bundle
                        bundles[bundleName] = new Dictionary<string, Dictionary<string, string>>();
                    }
                    if(!bundles[bundleName].ContainsKey(abbrev)) {
                        bundles[bundleName][abbrev] = new Dictionary<string, string>();
                    }
                    bundles[bundleName][abbrev][elementName] = reader["translation"].ToString();
                }
            }
            reader.Close();

            if(includedLanguages.Contains(defaultLanguage)) {
                if(includeSummary) {
                    string defaultLanguageName = null;
                    try {
                        defaultLanguageName = CultureInfo.GetCultureInfo(defaultLanguage).EnglishName;
                    } catch {
                        defaultLanguageName = defaultLanguage; // just use the abbreviation
                    }
                    summary.AppendFormat("*Included {0} ({1}), 100% complete\n", defaultLanguageName, defaultLanguage);
                }
                foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) { // the default language is loaded separately from the rest, merge them here
                    if(!bundles.ContainsKey(defaultBundle.Key)) { //  first element in this bundle
                        bundles[defaultBundle.Key] = new Dictionary<string, Dictionary<string, string>>();
                    }
                    if(!bundles[defaultBundle.Key].ContainsKey(defaultLanguage)) {
                        bundles[defaultBundle.Key][defaultLanguage] = new Dictionary<string, string>();
                    }
                    foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                        bundles[defaultBundle.Key][defaultLanguage][element.Key] = element.Value;
                    }
                }
            }

            // remove languages below the completion threshold
            List<string> incompleteLanguages = new List<string>();
            foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=number of translatedStrings
                int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                if(completionPercentage > threshold) {
                    if(includeSummary) {
                        string languageName = null;
                        try {
                            languageName = CultureInfo.GetCultureInfo(languageCompletion.Key).EnglishName;
                        } catch {
                            languageName = languageCompletion.Key;
                        }
                        summary.AppendFormat("*Included {0} ({1}), {2}% complete\n", languageName, languageCompletion.Key, completionPercentage);
                    }
                    foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) { // key=bundlename, val=Dictionary<element,translation>
                        if(!bundles.ContainsKey(bundle.Key)) {
                            bundles[bundle.Key] = new Dictionary<string, Dictionary<string, string>>();
                        }
                        if(!bundles[bundle.Key].ContainsKey(languageCompletion.Key)) {
                            bundles[bundle.Key].Add(languageCompletion.Key, bundle.Value);
                        } else {
                            foreach(KeyValuePair<string, string> element in bundle.Value) {
                                if(!bundles[bundle.Key][languageCompletion.Key].ContainsKey(element.Key)) {
                                    bundles[bundle.Key][languageCompletion.Key].Add(element.Key, element.Value);
                                }
                            }
                        }
                    }
                } else {
                    if(includeSummary) {
                        string languageName = null;
                        try {
                            languageName = CultureInfo.GetCultureInfo(languageCompletion.Key).EnglishName;
                        } catch {
                            languageName = languageCompletion.Key;
                        }
                        summary.AppendFormat("*Omitted {0} ({1}), {2}% complete\n", languageName, languageCompletion.Key, completionPercentage);
                    }
                    foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> form in bundles) {
                        form.Value.Remove(languageCompletion.Key);
                    }
                }
            }
            return new KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>>(includeSummary ? summary.ToString() : null, bundles);
        }
    }

    /// <summary>
    /// Outputs to Firefox .properties and .dtd files.
    /// </summary>
    /// <param name="file">The file to generate.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The included languages.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    public static void GenerateFirefox(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> resources = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, translation>>>
        List<string> errors = new List<string>();
        StringBuilder summary = null;
        if(includeSummary) {
            summary = new StringBuilder("Resources generated by ResourceBlender v").Append(Constants.GetVersion().ToString(3))
                                .Append(" on ").Append(DateTime.Now.ToShortDateString()).Append("\n");
        }

        Dictionary<string, int> completionMap = new Dictionary<string, int>();
        int totalStrings = 0;
        Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>();
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            StringBuilder sqlBuffer = new StringBuilder("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ");
            sqlBuffer.Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            cmd.AddParameterWithValue("@applicationId", applicationId);
            cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string bundleName = reader["bundlename"].ToString(); // JavaScript / XUL
                string elementName = reader["elementname"].ToString(); // eg: element
                if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this form name
                    defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                }
                defaultLanguageStrings[bundleName][elementName] = reader["translation"].ToString();
                totalStrings++;
            }
            reader.Close();
            if(includeSummary) {
                summary.AppendFormat("Firefox extension '{0}' contains {1} element{2} in {3} bundle{4}:\n", applicationName, totalStrings, totalStrings == 1 ? "" : "s", defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? "" : "s");
                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                    summary.AppendFormat("*{0} ({1} element{2})\n", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? "" : "s");
                }
                summary.AppendFormat("\nOmitting languages less than {0}% complete\n", threshold);
            }

            sqlBuffer.Remove(0, sqlBuffer.Length);
            sqlBuffer.Append("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ")
                     .Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang")
                     .Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string abbrev = reader["language"].ToString();
                string bundle = reader["bundle"].ToString();
                string bundleName = reader["bundlename"].ToString(); // JavaScript / XUL
                string elementName = reader["elementname"].ToString(); // eg: element
                if(includedBundles.Contains(bundle) && includedLanguages.Contains(abbrev)) {
                    if(!completionMap.ContainsKey(abbrev)) {
                        completionMap[abbrev] = 0;
                    }
                    completionMap[abbrev]++;
                    if(!resources.ContainsKey(abbrev)) { //  first element in this form name
                        resources[abbrev] = new Dictionary<string, Dictionary<string, string>>();
                    }
                    if(!resources[abbrev].ContainsKey(bundleName)) {
                        resources[abbrev][bundleName] = new Dictionary<string, string>();
                    }
                    resources[abbrev][bundleName][elementName] = reader["translation"].ToString();
                }
            }
            reader.Close();

            if(includedLanguages.Contains(defaultLanguage)) {
                if(includeSummary) {
                    string defaultLanguageName = null;
                    try {
                        defaultLanguageName = CultureInfo.GetCultureInfo(defaultLanguage).EnglishName;
                    } catch {
                        defaultLanguageName = defaultLanguage; // just use the abbreviation
                    }
                    summary.AppendFormat("*Included {0} ({1}), 100% complete\n", defaultLanguageName, defaultLanguage);
                }
                resources[defaultLanguage] = new Dictionary<string, Dictionary<string, string>>();
                foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) {
                    resources[defaultLanguage][defaultBundle.Key] = new Dictionary<string, string>();
                    foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                        resources[defaultLanguage][defaultBundle.Key][element.Key] = element.Value;
                    }
                }
            }

            // remove languages below the completion threshold
            List<string> incompleteLanguages = new List<string>();
            foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=translatedStrings
                int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                if(completionPercentage > threshold) {
                    if(includeSummary) {
                        string languageName = null;
                        try {
                            languageName = CultureInfo.GetCultureInfo(languageCompletion.Key).EnglishName;
                        } catch {
                            languageName = languageCompletion.Key;
                        }
                        summary.AppendFormat("*Included {0} ({1}), {2}% complete\n", languageName, languageCompletion.Key, completionPercentage);
                    }
                    if(!resources.ContainsKey(languageCompletion.Key)) { // there are no resources for this language, use the default language instead
                        resources[languageCompletion.Key] = defaultLanguageStrings;
                    } else {
                        foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                            if(!resources[languageCompletion.Key].ContainsKey(bundle.Key)) { // some resources exist, but this bundle does not, use the default language for this bundle
                                resources[languageCompletion.Key][bundle.Key] = bundle.Value;
                            } else {
                                foreach(KeyValuePair<string, string> element in bundle.Value) {
                                    if(!resources[languageCompletion.Key][bundle.Key].ContainsKey(element.Key)) {
                                        resources[languageCompletion.Key][bundle.Key].Add(element.Key, element.Value);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    if(includeSummary) {
                        string languageName = null;
                        try {
                            languageName = CultureInfo.GetCultureInfo(languageCompletion.Key).EnglishName;
                        } catch {
                            languageName = languageCompletion.Key;
                        }
                        summary.AppendFormat("*Omitted {0} ({1}), {2}% complete\n", languageCompletion.Key, languageName, completionPercentage);
                    }
                    resources.Remove(languageCompletion.Key);
                }
            }
        }
        using(MemoryStream ms = new MemoryStream()) {
            ZipOutputStream zipStream = new ZipOutputStream(ms);
            zipStream.SetLevel(9);
            Crc32 crc = new Crc32();
            StringBuilder resourceBuffer;

            foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> language in resources) {
                /* language.Key = language abbreviation
                   language.Value = Dictionary<bundleName, Dictionary<element, resource>> */
                ZipEntry zipEntry = new ZipEntry(language.Key + "\\contents.rdf");
                string cultureName = string.Empty;
                try {
                    cultureName = System.Globalization.CultureInfo.GetCultureInfo(language.Key).EnglishName;
                } catch {
                    cultureName = language.Key;
                }
                resourceBuffer = new StringBuilder();
                resourceBuffer.Append(string.Format(Constants.ContentsRDF, language.Key, cultureName, applicationName));
                zipEntry.DateTime = DateTime.Now;
                byte[] buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                zipEntry.Size = buffer.Length;

                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);

                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in language.Value) {
                    /* bundle.Key = bundleName
                       bundle.Value = Dictionary<element, resource> */
                    resourceBuffer = new StringBuilder();
                    switch(bundle.Key.ToLower()) {
                        case "javascript":
                            zipEntry = new ZipEntry(language.Key + "\\" + applicationName + ".properties");
                            foreach(KeyValuePair<string, string> jsElement in bundle.Value) {
                                resourceBuffer.Append(jsElement.Key).Append("=").Append(jsElement.Value).Append("\n");
                            }
                            break;
                        case "xul":
                            zipEntry = new ZipEntry(language.Key + "\\" + applicationName + ".dtd");
                            foreach(KeyValuePair<string, string> xulElement in bundle.Value) {
                                resourceBuffer.Append("<!ENTITY ").Append(xulElement.Key).Append(" \"").Append(xulElement.Value).Append("\">\n");
                            }
                            break;
                    }

                    zipEntry.DateTime = DateTime.Now;
                    buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                    zipEntry.Size = buffer.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);
                }
            }

            if(includeSummary) {
                ZipEntry zipEntry = new ZipEntry("summary.txt");
                zipEntry.DateTime = DateTime.Now;
                byte[] summaryBytes = Encoding.UTF8.GetBytes(summary.ToString());
                zipEntry.Size = summaryBytes.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(summaryBytes, 0, summaryBytes.Length);
            }
            zipStream.Finish();
            SaveZip(ms, file, !zipOutput);
            ms.Close();
        }
    }

    /// <summary>
    /// Outputs to .NET resx files.
    /// </summary>
    /// <param name="file">The file to generate.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The included bundles.</param>
    /// <param name="includedLanguages">The included languages.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    /// <param name="compactFramework"><c>true</c> if the target platform is the Compact Framework, otherwise <c>false</c>.</param>
    public static void GenerateDotNet(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary, bool compactFramework) {
        KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> rawResources = GenerateBundles(applicationName, applicationId, threshold, includedBundles, includedLanguages, defaultLanguage, includeSummary);
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> forms = rawResources.Value;

        using(MemoryStream ms = new MemoryStream()) {
            ZipOutputStream zipStream = new ZipOutputStream(ms);
            zipStream.SetLevel(9);
            Crc32 crc = new Crc32();

            foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> form in forms) {
                /* form.Key = bundlename
                   form.Value = Dictionary<abbrev, Dictionary<location, translation>> */
                foreach(KeyValuePair<string, Dictionary<string, string>> lang in form.Value) {
                    /* lang.Key = abbrev
                       lang.Value = Dictionary<location, translation> */
                    XmlDocument resx = new XmlDocument();
                    resx.LoadXml(Constants.ResxSkeleton);
                    XmlNode root = resx.SelectSingleNode("/root");

                    string langAbbreviation = string.Empty;
                    if(compactFramework) {
                        switch(lang.Key) {
                            case "zh-Hans": // buggy .NET Framework and contradictory MSDN articles
                                langAbbreviation = "zh-CHS";
                                break;
                            case "zh-Hant": // buggy .NET Framework and contradictory MSDN articles
                                langAbbreviation = "zh-CHT";
                                break;
                            default:
                                langAbbreviation = lang.Key;
                                break;
                        }
                    } else {
                        langAbbreviation = lang.Key;
                    }
                    List<string> addedElements = new List<string>();
                    /*String templateFilePath = HttpContext.Current.Server.MapPath(Path.Combine(Path.Combine("templates", applicationName), form.Key + "." + langAbbreviation + ".resx")); // try specific language first
                    if(!File.Exists(templateFilePath)) { // just use the default
                        templateFilePath = HttpContext.Current.Server.MapPath(Path.Combine(Path.Combine("templates", applicationName), form.Key + ".resx"));
                    }
                    if(File.Exists(templateFilePath)) {
                        XmlDocument templateFile = new XmlDocument();
                        templateFile.Load(templateFilePath);
                        XmlNodeList existingValues = templateFile.SelectNodes("/root/data");
                        foreach(XmlNode node in existingValues) {
                            XmlNode newNode = resx.ImportNode(node, true);
                            root.AppendChild(newNode);
                            //TODO: check if the attribute doesn't exist - is exception thrown?
                            addedElements.Add(node.Attributes.GetNamedItem("name").InnerText);
                        }
                    }*/
                    foreach(KeyValuePair<string, string> translatedString in lang.Value) {
       //                 if(!addedElements.Contains(translatedString.Key)) {
                            XmlNode nodeResourceData = resx.CreateElement("data");
                            nodeResourceData.Attributes.SetNamedItem(resx.CreateAttribute("name")).InnerText = translatedString.Key;
                            nodeResourceData.AppendChild(resx.CreateElement("value")).InnerText = translatedString.Value.Replace("\\n", "\n");
                            root.AppendChild(nodeResourceData);
             //           }
                    }

                    ZipEntry zipEntry;
                    using(MemoryStream msEntry = new MemoryStream()) {
                        zipEntry = new ZipEntry(form.Key + "." + langAbbreviation + ".resx");
                        zipEntry.DateTime = DateTime.Now;
                        resx.Save(msEntry);
                        byte[] buffer = msEntry.ToArray();
                        msEntry.Close();
                        msEntry.Dispose();
                        zipEntry.Size = buffer.Length;

                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);

                        if(form.Key == "General" && lang.Key == defaultLanguage) { // todo: move to meta field in DB? universal fields?
                            zipEntry = new ZipEntry(form.Key + ".resx");
                            zipEntry.DateTime = DateTime.Now;
                            zipEntry.Size = buffer.Length;
                            zipStream.PutNextEntry(zipEntry);
                            zipStream.Write(buffer, 0, buffer.Length);
                        }
                    }
                }
            }

            if(includeSummary) {
                ZipEntry zipEntry = new ZipEntry("summary.txt");
                zipEntry.DateTime = DateTime.Now;
                byte[] summaryBytes = Encoding.UTF8.GetBytes(rawResources.Key.ToString());
                zipEntry.Size = summaryBytes.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(summaryBytes, 0, summaryBytes.Length);
            }
            zipStream.Finish();
            SaveZip(ms, file, !zipOutput);
            ms.Close();
        }
    }


    /// <summary>
    /// Outputs bundles to Java .properties files. This could be done using a template, but Java uses subtly different language codes
    /// </summary>
    /// <param name="file">The file to generate.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The included bundles.</param>
    /// <param name="includedLanguages">The included languages.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    public static void GenerateJava(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> rawResources = GenerateBundles(applicationName, applicationId, threshold, includedBundles, includedLanguages, defaultLanguage, includeSummary);
        using(MemoryStream ms = new MemoryStream()) {
            ZipOutputStream zipStream = new ZipOutputStream(ms);
            zipStream.SetLevel(9);
            Crc32 crc = new Crc32();
            StringBuilder resourceBuffer;
            ZipEntry zipEntry;
            byte[] buffer;

            Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = rawResources.Value;
            foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> bundle in bundles) {
                /* bundle.Key = bundlename
                   bundle.Value = Dictionary<abbrev, Dictionary<element, resource>> */
                foreach(KeyValuePair<string, Dictionary<string, string>> lang in bundle.Value) {
                    /* lang.Key = abbrev
                       lang.Value = Dictionary<element, resource> */
                    resourceBuffer = new StringBuilder();
                    string langAbbreviation = Utils.DotNetCulture2JavaCulture(lang.Key);

                    zipEntry = new ZipEntry(bundle.Key + "_" + langAbbreviation + ".properties");
                    zipEntry.DateTime = DateTime.Now;
                    foreach(KeyValuePair<string, string> resource in lang.Value) {
                        resourceBuffer.Append(resource.Key).Append("=").AppendLine(resource.Value);
                    }
                    buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                    zipEntry.Size = buffer.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);
                }
            }

            if(rawResources.Key != null) {
                zipEntry = new ZipEntry("summary.txt");
                zipEntry.DateTime = DateTime.Now;
                byte[] summaryBytes = Encoding.UTF8.GetBytes(rawResources.Key);
                zipEntry.Size = summaryBytes.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(summaryBytes, 0, summaryBytes.Length);
            }
            zipStream.Finish();
            SaveZip(ms, file, !zipOutput);
            ms.Close();
        }
    }


    /// <summary>
    /// Generates resources using a custom template.
    /// </summary>
    /// <param name="file">The file to generate.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="rawResources">The raw resources.</param>
    /// <param name="fileName">Name of each file.</param>
    /// <param name="fileStart">The text to prepend to the start of each file.</param>
    /// <param name="fileEnd">The text to append to the end of each file.</param>
    /// <param name="lineFormat">The line format.</param>
    public static void GenerateCustom(FileInfo file, bool zipOutput, string applicationName, KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> rawResources, string fileName, string fileStart, string fileEnd, string lineFormat) {
        using(MemoryStream ms = new MemoryStream()) {
            ZipOutputStream zipStream = new ZipOutputStream(ms);
            zipStream.SetLevel(9);
            Crc32 crc = new Crc32();
            StringBuilder resourceBuffer;
            ZipEntry zipEntry;
            byte[] buffer;

            string[] bundleTags = new string[] { "{=LanguageCode}",
                                                     "{=Bundle}" }, // valid only for the current bundle (filename, filestart, fileend)
                     elementTags = new string[] { "{=LanguageCode}",
                                                      "{=Bundle}",
                                                      "{=Element}",
                                                      "{=Translation}"
                                                       }, // valid for each element
                     replacementBundleTags = new string[] { "", "" },
                     replacementElementTags = new string[] { "", "", "", "" };
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = rawResources.Value;
            foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> bundle in bundles) {
                /* bundle.Key = bundlename
                   bundle.Value = Dictionary<abbrev, Dictionary<element, translation>> */
                replacementBundleTags[1] = bundle.Key;
                replacementElementTags[1] = bundle.Key;
                foreach(KeyValuePair<string, Dictionary<string, string>> lang in bundle.Value) {
                    /* lang.Key = abbrev
                       lang.Value = Dictionary<element, translation> */
                    replacementBundleTags[0] = lang.Key;
                    replacementElementTags[0] = lang.Key;
                    resourceBuffer = new StringBuilder();
                    if(fileStart.Length > 0) {
                        resourceBuffer.AppendLine(Utils.FormatTemplateTags(fileStart, bundleTags, replacementBundleTags));
                    }
                    zipEntry = new ZipEntry(Utils.FormatTemplateTags(fileName, bundleTags, replacementBundleTags));
                    zipEntry.DateTime = DateTime.Now;
                    foreach(KeyValuePair<string, string> resource in lang.Value) {
                        replacementElementTags[2] = resource.Key;
                        replacementElementTags[3] = resource.Value;
                        resourceBuffer.AppendLine(Utils.FormatTemplateTags(lineFormat, elementTags, replacementElementTags));
                    }
                    if(fileEnd.Length > 0) {
                        resourceBuffer.AppendLine(Utils.FormatTemplateTags(fileEnd, bundleTags, replacementBundleTags));
                    }
                    buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                    zipEntry.Size = buffer.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);
                }
            }

            string summary = rawResources.Key;
            if(summary != null) {
                zipEntry = new ZipEntry("summary.txt");
                zipEntry.DateTime = DateTime.Now;
                byte[] summaryBytes = Encoding.UTF8.GetBytes(summary);
                zipEntry.Size = summaryBytes.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(summaryBytes, 0, summaryBytes.Length);
            }
            zipStream.Finish();
            SaveZip(ms, file, !zipOutput);
            ms.Close();
        }
    }

    /// <summary>
    /// Outputs to GNU PO files.
    /// </summary>
    /// <param name="file">The file to generate.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The included languages.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    public static void GeneratePO(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> resources = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, translation>>>
        List<string> errors = new List<string>();
        StringBuilder summary = null;
        if(includeSummary) {
            summary = new StringBuilder("Resources generated by ResourceBlender v").Append(Constants.GetVersion().ToString(3))
                                .Append(" on ").Append(DateTime.Now.ToShortDateString()).Append("\n");
        }

        Dictionary<string, int> completionMap = new Dictionary<string, int>();
        int totalStrings = 0;
        Dictionary<string, Dictionary<string, POMetaData>> elementData = new Dictionary<string, Dictionary<string, POMetaData>>(); // <BundleName, <ElementName, Element meta-data>>
        Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>(); // <BundleName, <ElementName, Untranslated string>>
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            StringBuilder sqlBuffer = new StringBuilder("SELECT language, bundle, elementname, translatorcomment, comment AS metadata, translation, b.name AS bundlename FROM ");
            sqlBuffer.Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            cmd.AddParameterWithValue("@applicationId", applicationId);
            cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for entries in the default language (untranslated strings)
                string bundleName = reader["bundlename"].ToString(),
                       elementName = reader["elementname"].ToString(),
                       rawMetaData = reader["metadata"].ToString(),
                       translation = reader["translation"].ToString(),
                       translatorComment = reader["translatorcomment"].ToString();
                if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this bundle
                    defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                    elementData[bundleName] = new Dictionary<string, POMetaData>();
                }
                defaultLanguageStrings[bundleName][elementName] = translation;
                POMetaData data = Utils.ParsePOMetaData(rawMetaData);
                if(translatorComment.Length > 0) {
                    data.TranslatorsComments.Add(defaultLanguage, translatorComment);
                }
                data.UntranslatedString = translation;
                elementData[bundleName][elementName] = data;
                totalStrings++;
            }
            reader.Close();
            if(includeSummary) {
                summary.AppendFormat("Application '{0}' contains {1} element{2} in {3} bundle{4}:\n", applicationName, totalStrings, totalStrings == 1 ? "" : "s", defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? "" : "s");
                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                    summary.AppendFormat("*{0} ({1} element{2})\n", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? "" : "s");
                }
                summary.AppendFormat("\nOmitting languages less than {0}% complete\n", threshold);
            }

            sqlBuffer.Remove(0, sqlBuffer.Length);
            sqlBuffer.Append("SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ")
                     .Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang")
                     .Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string abbrev = reader["language"].ToString();
                string bundle = reader["bundle"].ToString();
                string bundleName = reader["bundlename"].ToString();
                string elementName = reader["elementname"].ToString(); // eg: element
                string translatorComment = reader["translatorcomment"].ToString();
                if(!defaultLanguageStrings.ContainsKey(bundleName) || !defaultLanguageStrings[bundleName].ContainsKey(elementName)) {
                    continue; // the default language is missing, so just skip this element
                }
                if(includedBundles.Contains(bundle) && includedLanguages.Contains(abbrev)) {
                    if(!completionMap.ContainsKey(abbrev)) {
                        completionMap[abbrev] = 0;
                    }
                    completionMap[abbrev]++;
                    if(!resources.ContainsKey(abbrev)) { // first element in this bundle
                        resources[abbrev] = new Dictionary<string, Dictionary<string, string>>();
                    }
                    if(!resources[abbrev].ContainsKey(bundleName)) {
                        resources[abbrev][bundleName] = new Dictionary<string, string>();
                    }
                    resources[abbrev][bundleName][elementName] = reader["translation"].ToString();
                    if(translatorComment.Length > 0) {
                        elementData[bundleName][elementName].TranslatorsComments.Add(abbrev, translatorComment);
                    }
                }
            }
            reader.Close();

            if(includedLanguages.Contains(defaultLanguage)) {
                if(includeSummary) {
                    string defaultLanguageName = null;
                    try {
                        defaultLanguageName = CultureInfo.GetCultureInfo(defaultLanguage).EnglishName;
                    } catch {
                        defaultLanguageName = defaultLanguage; // just use the abbreviation
                    }
                    summary.AppendFormat("*Included {0} ({1}), 100% complete\n", defaultLanguageName, defaultLanguage);
                }
                resources[defaultLanguage] = new Dictionary<string, Dictionary<string, string>>();
                foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) {
                    resources[defaultLanguage][defaultBundle.Key] = new Dictionary<string, string>();
                    foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                        resources[defaultLanguage][defaultBundle.Key][element.Key] = element.Value;
                    }
                }
            }

            // remove languages below the completion threshold
            List<string> incompleteLanguages = new List<string>();
            foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=translatedStrings
                int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                if(completionPercentage > threshold) {
                    if(includeSummary) {
                        string languageName = null;
                        try {
                            languageName = CultureInfo.GetCultureInfo(languageCompletion.Key).EnglishName;
                        } catch {
                            languageName = languageCompletion.Key;
                        }
                        summary.AppendFormat("*Included {0} ({1}), {2}% complete\n", languageName, languageCompletion.Key, completionPercentage > 100 ? 100 : completionPercentage);
                    }
                    if(!resources.ContainsKey(languageCompletion.Key)) { // there are no resources for this language, use the default language instead
                        resources[languageCompletion.Key] = resources[defaultLanguage];
                    } else {
                        foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) { // <BundleName, <ElementName, Meta-Data>>
                            if(!resources[languageCompletion.Key].ContainsKey(bundle.Key)) { // some resources exist, but this bundle does not, use the default language for this bundle
                                resources[languageCompletion.Key][bundle.Key] = defaultLanguageStrings[bundle.Key];
                            } else {
                                foreach(KeyValuePair<string, string> element in bundle.Value) {
                                    if(!resources[languageCompletion.Key][bundle.Key].ContainsKey(element.Key)) {
                                        resources[languageCompletion.Key][bundle.Key].Add(element.Key, element.Value);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    if(includeSummary) {
                        string languageName = null;
                        try {
                            languageName = CultureInfo.GetCultureInfo(languageCompletion.Key).EnglishName;
                        } catch {
                            languageName = languageCompletion.Key;
                        }
                        summary.AppendFormat("*Omitted {0} ({1}), {2}% complete\n", languageCompletion.Key, languageName, completionPercentage > 100 ? 100 : completionPercentage);
                    }
                    resources.Remove(languageCompletion.Key);
                }
            }
        }

        using(MemoryStream ms = new MemoryStream()) {
            ZipOutputStream zipStream = new ZipOutputStream(ms);
            zipStream.SetLevel(9);
            Crc32 crc = new Crc32();
            StringBuilder resourceBuffer = new StringBuilder(); ;

            foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> language in resources) {
                /* language.Key = language abbreviation
                   language.Value = Dictionary<bundleName, Dictionary<element, resource>> */
                ZipEntry zipEntry;
                byte[] buffer;
                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in language.Value) {
                    /* bundle.Key = bundleName
                       bundle.Value = Dictionary<element, resource> */
                    resourceBuffer.Remove(0, resourceBuffer.Length);
                    zipEntry = new ZipEntry(bundle.Key + "\\" + language.Key + ".po");
                    foreach(KeyValuePair<string, string> element in bundle.Value) {
                        POMetaData metaData = elementData[bundle.Key][element.Key];
                        if(resourceBuffer.Length > 0) { // omit whitespace at start of file
                            resourceBuffer.Append("\n\n");
                        }
                        if(metaData.TranslatorsComments.ContainsKey(language.Key)) {
                            string translatorComments = metaData.TranslatorsComments[language.Key];
                            if(translatorComments.Length > 0) {
                                foreach(string commentLine in translatorComments.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries)) {
                                    resourceBuffer.Append("# ").Append(commentLine).Append("\n");
                                }
                            }
                        }
                        if(metaData.ExtractedComments.Count > 0) {
                            foreach(string comment in metaData.ExtractedComments) {
                                resourceBuffer.Append("#. ").Append(comment).Append("\n");
                            }
                        }
                        if(metaData.References.Count > 0) {
                            foreach(string reference in metaData.References) {
                                resourceBuffer.Append("#: ").Append(reference).Append("\n");
                            }
                        }
                        if(metaData.Flags.Count > 0) {
                            foreach(string flag in metaData.Flags) {
                                resourceBuffer.Append("#, ").Append(flag).Append("\n");
                            }
                        }
                        if(metaData.Context != null) {
                            resourceBuffer.Append("msgctxt \"").Append(metaData.Context).Append("\"\n");
                        }
                        string msgId = element.Key == "Generator" ? "" : metaData.UntranslatedString.Replace("\\n", "\\n\"\n\"");
                        if(msgId.EndsWith("\n\"")) {
                            msgId = msgId.Substring(0, msgId.Length - 2);
                        }
                        string msgStr = element.Value.Contains("\\n") ? element.Value.Replace("\\n", "\\n\"\n\"") : element.Value; // if it's a multiline string, replace linefeeds with \n"<newline>"
                        if(msgStr.EndsWith("\n\"")) {
                            msgStr = msgStr.Substring(0, msgStr.Length - 3);
                        }
                        resourceBuffer.AppendFormat("msgid \"{0}\"\nmsgstr \"{1}\"", msgId, msgStr);
                    }

                    zipEntry.DateTime = DateTime.Now;
                    buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                    zipEntry.Size = buffer.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);
                }

                if(includeSummary) {
                    zipEntry = new ZipEntry("summary.txt");
                    zipEntry.DateTime = DateTime.Now;
                    byte[] summaryBytes = Encoding.UTF8.GetBytes(summary.ToString());
                    zipEntry.Size = summaryBytes.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(summaryBytes, 0, summaryBytes.Length);
                }
            }
            zipStream.Finish();
            SaveZip(ms, file, !zipOutput);
            ms.Close();
        }
    }

    /// <summary>
    /// Exports to ResourceBlender XML format.
    /// </summary>
    /// <param name="file">The file to which to export.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped</param>
    /// <param name="includeApps"><c>true</c> if applications should be included</param>
    /// <param name="includeElements"><c>true</c> if elements should be included</param>
    /// <param name="includeResources"><c>true</c> if resources should be included</param>
    /// <param name="includeTemplates"><c>true</c> if templates should be included</param>
    /// <param name="includeUsers"><c>true</c> if users should be included</param>
    public static void GenerateXML(FileInfo file, bool zipOutput, bool includeApps, bool includeElements, bool includeResources, bool includeTemplates, bool includeUsers) {
        int numApps = 0,
            numBundles = 0,
            numElements = 0,
            numResources = 0,
            numTemplates = 0,
            numTranslations = 0,
            numUsers = 0;
        Database db = null;
        try {
            db = new Database();
            db.OpenConnection();
            XmlDataDocument doc = new XmlDataDocument();
            XmlDeclaration decl = (XmlDeclaration)doc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
            decl.Encoding = "UTF-8";
            doc.AppendChild(decl);
            XmlNode root = doc.CreateElement("resourceblender");
            doc.AppendChild(root);

            XmlElement ndMeta = doc.CreateElement("meta");
            root.AppendChild(ndMeta);
            XmlElement ndGenerator = doc.CreateElement("generator");
            ndGenerator.InnerText = "ResourceBlender Express " + Constants.GetVersion().ToString(3);
            ndMeta.AppendChild(ndGenerator);
            XmlElement ndGenerationDate = doc.CreateElement("generated");
            ndGenerationDate.InnerText = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss G\\MT");
            ndMeta.AppendChild(ndGenerationDate);

            if(includeApps) {
                using(Database.Command cmd = db.CreateCommand()) {
                    XmlElement ndApplications = doc.CreateElement("applications");
                    root.AppendChild(ndApplications);
                    cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "applications";
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        XmlElement eleApplication = doc.CreateElement("application");
                        ndApplications.AppendChild(eleApplication);

                        XmlElement eleId = doc.CreateElement("id");
                        eleId.InnerText = Utils.HtmlEncode(reader["id"].ToString());
                        eleApplication.AppendChild(eleId);

                        XmlElement eleName = doc.CreateElement("name");
                        eleName.InnerText = Utils.HtmlEncode(reader["name"].ToString());
                        eleApplication.AppendChild(eleName);

                        XmlElement eleDisplayname = doc.CreateElement("displayname");
                        eleDisplayname.InnerText = Utils.HtmlEncode(reader["displayname"].ToString());
                        eleApplication.AppendChild(eleDisplayname);

                        XmlElement eleDefaultLanguage = doc.CreateElement("defaultlanguage");
                        eleDefaultLanguage.InnerText = Utils.HtmlEncode(reader["defaultlanguage"].ToString());
                        eleApplication.AppendChild(eleDefaultLanguage);

                        XmlElement eleLanguages = doc.CreateElement("languages");
                        eleLanguages.InnerText = Utils.HtmlEncode(reader["languages"].ToString());
                        eleApplication.AppendChild(eleLanguages);
                        numApps++;
                    }
                    reader.Close();
                    XmlElement ndBundles = doc.CreateElement("bundles");
                    root.AppendChild(ndBundles);
                    cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "bundles";
                    reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        XmlElement eleBundle = doc.CreateElement("bundle");
                        ndBundles.AppendChild(eleBundle);

                        XmlElement eleId = doc.CreateElement("id");
                        eleId.InnerText = Utils.HtmlEncode(reader["id"].ToString());
                        eleBundle.AppendChild(eleId);

                        XmlElement eleName = doc.CreateElement("name");
                        eleName.InnerText = Utils.HtmlEncode(reader["name"].ToString());
                        eleBundle.AppendChild(eleName);

                        XmlElement eleApplicationId = doc.CreateElement("application");
                        eleApplicationId.InnerText = Utils.HtmlEncode(reader["application"].ToString());
                        eleBundle.AppendChild(eleApplicationId);
                        numBundles++;
                    }
                    reader.Close();
                }
            }
            if(includeElements) {
                using(Database.Command cmd = db.CreateCommand()) {
                    XmlElement ndElements = doc.CreateElement("elements");
                    root.AppendChild(ndElements);
                    cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "elements";
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        XmlElement eleElement = doc.CreateElement("element");
                        ndElements.AppendChild(eleElement);

                        XmlElement eleApplicationId = doc.CreateElement("applicationid");
                        eleApplicationId.InnerText = Utils.HtmlEncode(reader["applicationid"].ToString());
                        eleElement.AppendChild(eleApplicationId);

                        XmlElement eleBundleName = doc.CreateElement("bundle");
                        eleBundleName.InnerText = Utils.HtmlEncode(reader["bundle"].ToString());
                        eleElement.AppendChild(eleBundleName);

                        XmlElement eleElementName = doc.CreateElement("elementname");
                        eleElementName.InnerText = Utils.HtmlEncode(reader["elementname"].ToString());
                        eleElement.AppendChild(eleElementName);

                        XmlElement eleResourceId = doc.CreateElement("resourceid");
                        eleResourceId.InnerText = reader["resourceid"].ToString();
                        eleElement.AppendChild(eleResourceId);

                        XmlElement eleComment = doc.CreateElement("comment");
                        eleComment.InnerText = Utils.HtmlEncode(reader["comment"].ToString());
                        eleElement.AppendChild(eleComment);
                        numElements++;
                    }
                    reader.Close();
                }
            }
            if(includeResources) {
                using(Database.Command cmd = db.CreateCommand()) {
                    XmlElement ndResources = doc.CreateElement("resources");
                    root.AppendChild(ndResources);
                    cmd.CommandText = "SELECT r.resourceid AS resourceid, language, translation, translatorcomment, comment FROM " + db.TablePrefix + "resources AS r LEFT JOIN " + db.TablePrefix + "resourcecomments AS c ON r.resourceid = c.resourceid ORDER BY r.resourceid ASC";
                    DbDataReader reader = cmd.ExecuteReader();
                    string lastResourceId = string.Empty,
                           currentResourceId = string.Empty;
                    XmlElement eleResource = null;
                    while(reader.Read()) {
                        currentResourceId = reader["resourceid"].ToString();
                        if(currentResourceId != lastResourceId) {
                            eleResource = doc.CreateElement("resource");
                            ndResources.AppendChild(eleResource);

                            XmlElement eleId = doc.CreateElement("id");
                            eleId.InnerText = Utils.HtmlEncode(currentResourceId);
                            eleResource.AppendChild(eleId);

                            XmlElement eleComment = doc.CreateElement("comment");
                            eleComment.InnerText = Utils.HtmlEncode(reader["comment"].ToString());
                            eleResource.AppendChild(eleComment);

                            XmlElement eleLanguages = doc.CreateElement("languages");
                            eleResource.AppendChild(eleLanguages);
                            numTranslations++;
                        }
                        lastResourceId = currentResourceId;
                        XmlElement eleTranslatedLanguage = doc.CreateElement("language");
                        eleTranslatedLanguage.InnerText = Utils.HtmlEncode(reader["translation"].ToString());
                        eleResource.LastChild.AppendChild(eleTranslatedLanguage);

                        XmlAttribute attrAbbreviation = doc.CreateAttribute("code");
                        attrAbbreviation.InnerText = reader["language"].ToString();
                        eleTranslatedLanguage.Attributes.Append(attrAbbreviation);

                        string translatorComment = reader["translatorcomment"].ToString();
                        if(translatorComment.Length > 0) {
                            XmlAttribute attrTranslatorComment = doc.CreateAttribute("translatorcomment");
                            attrTranslatorComment.InnerText = Utils.HtmlEncode(translatorComment);
                            eleTranslatedLanguage.Attributes.Append(attrTranslatorComment);
                        }
                        numResources++;
                    }
                    reader.Close();
                }
            }
            if(includeTemplates) {
                using(Database.Command cmd = db.CreateCommand()) {
                    XmlElement ndElements = doc.CreateElement("templates");
                    root.AppendChild(ndElements);
                    cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "templates";
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        XmlElement eleElement = doc.CreateElement("template");
                        ndElements.AppendChild(eleElement);

                        XmlElement eleTemplateName = doc.CreateElement("name");
                        eleTemplateName.InnerText = Utils.HtmlEncode(reader["name"].ToString());
                        eleElement.AppendChild(eleTemplateName);

                        XmlElement eleFileName = doc.CreateElement("filename");
                        eleFileName.InnerText = Utils.HtmlEncode(reader["filename"].ToString());
                        eleElement.AppendChild(eleFileName);

                        XmlElement eleFileStart = doc.CreateElement("filestart");
                        eleFileStart.InnerText = Utils.HtmlEncode(reader["filestart"].ToString());
                        eleElement.AppendChild(eleFileStart);

                        XmlElement eleFileEnd = doc.CreateElement("fileend");
                        eleFileEnd.InnerText = Utils.HtmlEncode(reader["fileend"].ToString());
                        eleElement.AppendChild(eleFileEnd);

                        XmlElement eleLine = doc.CreateElement("line");
                        eleLine.InnerText = Utils.HtmlEncode(reader["line"].ToString());
                        eleElement.AppendChild(eleLine);
                        numTemplates++;
                    }
                    reader.Close();
                }
            }
            if(includeUsers) {
                using(Database.Command cmd = db.CreateCommand()) {
                    XmlElement ndUsers = doc.CreateElement("users");
                    root.AppendChild(ndUsers);
                    cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "users";
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        XmlElement eleUser = doc.CreateElement("user");
                        ndUsers.AppendChild(eleUser);

                        XmlElement eleId = doc.CreateElement("id");
                        eleId.InnerText = Utils.HtmlEncode(reader["id"].ToString());
                        eleUser.AppendChild(eleId);

                        XmlElement eleUsername = doc.CreateElement("username");
                        eleUsername.InnerText = Utils.HtmlEncode(reader["username"].ToString());
                        eleUser.AppendChild(eleUsername);

                        XmlElement elePassword = doc.CreateElement("password");
                        elePassword.InnerText = Utils.HtmlEncode(reader["password"].ToString());
                        eleUser.AppendChild(elePassword);

                        XmlElement eleEmail = doc.CreateElement("email");
                        eleEmail.InnerText = Utils.HtmlEncode(reader["email"].ToString());
                        eleUser.AppendChild(eleEmail);

                        XmlElement elePermissions = doc.CreateElement("permissions");
                        elePermissions.InnerText = Utils.HtmlEncode(reader["permissions"].ToString());
                        eleUser.AppendChild(elePermissions);

                        XmlElement eleAllowedProjects = doc.CreateElement("allowedprojects");
                        eleAllowedProjects.InnerText = Utils.HtmlEncode(reader["allowedprojects"].ToString());
                        eleUser.AppendChild(eleAllowedProjects);
                        numUsers++;
                    }
                    reader.Close();
                }
            }



            using(MemoryStream msDoc = new MemoryStream())
            using(MemoryStream msZip = new MemoryStream()) {
                ZipOutputStream zipStream = new ZipOutputStream(msZip);
                zipStream.SetLevel(9);

                doc.Save(msDoc);
                byte[] buffer = msDoc.ToArray();
                msDoc.Close();
                ZipEntry zipEntry = new ZipEntry("ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ".xml");
                zipEntry.DateTime = DateTime.Now;
                zipEntry.Size = buffer.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);
                zipStream.Finish();
                SaveZip(msZip, file, !zipOutput);
                msZip.Close();
            }
        } catch(Exception ex) {
            db.CloseConnection();
            throw new Exception("Error #455: " + ex.Message, ex.InnerException);
        } finally {
            if(db != null) {
                try {
                    db.CloseConnection();
                } catch { }
            }
        }
    }

    /// <summary>
    /// Exports resources to an XML spreadsheet
    /// </summary>
    public static string ExportToExcel(string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, bool zipOutput, FileInfo file) {
        Database db = null;
        string error = string.Empty;
        try {
            db = new Database();
            db.OpenConnection();

            ExcelXmlWorkbook wb = new ExcelXmlWorkbook();
            List<int> addedIds = new List<int>();
            wb.Properties.Title = "ResourceBlender Resources (" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ")";
            using(Database.Command cmdApplication = db.CreateCommand())
            using(Database.Command cmdResources = db.CreateCommand()) {
                cmdApplication.CommandText = "SELECT displayname, defaultLanguage, languages FROM " + db.TablePrefix + "applications WHERE id = @p";
                cmdApplication.AddParameterWithValue("@p", "");
                cmdResources.AddParameterWithValue("@p", "");
                string applicationId = null,
                       applicationName = null,
                       defaultLanguage = null;
                string[] allLanguagesInApplication = null;
                int startingColumn = 1;
                List<string> selectedLanguagesInApplication = new List<string>(); // if just one list of languages is used, there are gaps for columns where a language from another application is not present in the current application
                XmlStyle greenBackground = new XmlStyle(),
                         greyBackground = new XmlStyle();
                greenBackground.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221);
                greenBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Interior.Color = System.Drawing.Color.FromArgb(240, 240, 240);
                greyBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Font.Color = System.Drawing.Color.FromArgb(130, 130, 130);
                for(int i = 0; i < applicationIds.Length; i++) {
                    selectedLanguagesInApplication.Clear();
                    applicationId = applicationIds[i];
                    cmdApplication.Parameters["@p"].Value = applicationId;
                    cmdResources.Parameters["@p"].Value = applicationId;
                    using(DbDataReader reader = cmdApplication.ExecuteReader(CommandBehavior.SingleResult)) {
                        while(reader.Read()) {
                            applicationName = reader.GetString(0);
                            defaultLanguage = reader.GetString(1);
                            allLanguagesInApplication = reader.GetString(2).Trim().Split(',');
                        }
                        reader.Close();
                    }
                    if(applicationName == null || applicationName.Length == 0) {
                        throw new ArgumentNullException("Application not found");
                    }
                    selectedLanguagesInApplication.Add(defaultLanguage);

                    int rowIndex = 1;
                    Worksheet sheet = wb[i];
                    sheet.Name = applicationName;
                    Row row = sheet.AddRow();
                    int column = startingColumn + 1;
                    sheet[startingColumn, 0].Value = "Resource ID";
                    Cell headerCell = sheet[column++, 0];
                    headerCell.Value = defaultLanguage;
                    foreach(string lang in allLanguagesInApplication) {
                        if(selectedLanguages.Contains(lang)) {
                            if(lang != defaultLanguage) {
                                selectedLanguagesInApplication.Add(lang);
                                headerCell = sheet[column++, 0];
                                headerCell.Value = lang;
                            }
                        }
                    }
                    sheet.FreezeTopRows = 1;
                    if(selectedLanguagesInApplication.Count == 0) {
                        throw new ArgumentException("No languages found for appliication:" + applicationName);
                    }

                    //sheet.Table.Columns[startingColumn].Width = 200;
                    cmdResources.CommandText = new StringBuilder("SELECT DISTINCT e.resourceid, e.id, r.language, r.translation, r.translatorcomment, c.comment FROM ")
                                                                .Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ")
                                                                .Append(db.TablePrefix).Append("resourcecomments AS c ON c.resourceid = r.resourceid WHERE applicationid = @p AND language IN('")
                                                                .Append(Utils.Implode(selectedLanguagesInApplication, "','", false)).Append("') AND bundle IN('").Append(Utils.Implode(selectedBundles, "','", false)).Append("') ORDER BY e.id").ToString();
                    using(DbDataReader reader = cmdResources.ExecuteReader()) {
                        int lastIdRead = -1;
                        while(reader.Read()) {
                            int resourceId = int.Parse(reader["resourceid"].ToString());
                            Cell cell = null;
                            if(lastIdRead == -1) {
                                if(addedIds.Contains(resourceId)) {
                                    continue;
                                }
                                addedIds.Add(resourceId);
                                lastIdRead = resourceId;
                                cell = sheet[startingColumn, rowIndex];
                                cell.Value = resourceId;
                                cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                cell.Style = greyBackground;
                            } else if(lastIdRead != resourceId) {
                                if(addedIds.Contains(resourceId)) {
                                    continue;
                                }
                                addedIds.Add(resourceId);
                                lastIdRead = resourceId;
                                cell = sheet[startingColumn, ++rowIndex];
                                cell.Value = resourceId;
                                cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                cell.Style = greyBackground;
                            }
                            cell = sheet[selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1, rowIndex];
                            //System.Diagnostics.Debug.WriteLine(reader["language"].ToString()+"\t"+selectedLanguagesInApplication.IndexOf(reader["language"].ToString())+"\t"+selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1);
                            cell.Value = reader["translation"].ToString().Replace("<", "&lt;").Replace(">", "&gt;"); //HttpUtility.HtmlEncode(reader["translation"].ToString());
                            cell.Comment = reader["translatorcomment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                        }
                        reader.Close();
                    }
                    Range defaultColumn = new Range(sheet[startingColumn + 1, 0], sheet[startingColumn + 1, rowIndex]);
                    defaultColumn.Style = greenBackground;
                    Range headerCells = new Range(sheet[startingColumn, 0], sheet[column, 0]);
                    headerCells.Font.Bold = true;
                }
            }

            using(MemoryStream msXml = new MemoryStream()) // holding stream for workbook
            using(MemoryStream msZip = new MemoryStream()) { // output stream for zip
                ZipOutputStream zipStream = new ZipOutputStream(msZip);
                zipStream.SetLevel(9);

                wb.Export(msXml);
                byte[] buffer = msXml.ToArray();
                msXml.Close();
                string fileName;
                if(zipOutput) {
                    fileName = "ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ".xml";
                } else {
                    fileName = file.Name;
                }
                ZipEntry zipEntry = new ZipEntry(fileName);
                zipEntry.DateTime = DateTime.Now;
                zipEntry.Size = buffer.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);
                zipStream.Finish();
                SaveZip(msZip, file, !zipOutput);
                msZip.Close();
            }
        } catch(Exception ex) {
            error = ex.Message + ex.StackTrace;
        } finally {
            if(db != null) {
                db.CloseConnection();
            }
        }
        return error;
    }

    /// <summary>
    /// Saves a ZipOutputStream, preserving it's structure.
    /// </summary>
    /// <param name="stream">The underlying MemoryStream.</param>
    /// <param name="outputFile">The output file (or directory).</param>
    /// <param name="extractZip"><c>true</c> if the zip should be extracted, otherwise <c>false</c>.</param>
    public static void SaveZip(MemoryStream stream, FileInfo outputFile, bool extractZip) {
        if(Directory.Exists(outputFile.FullName)) { // it's a directory
            ExtractZip(stream, outputFile.Directory.FullName);
        } else {
            FileStream fs;
            if(extractZip) { // extract the zip to the directory specified
                ExtractZip(stream, outputFile.Directory.FullName);
            } else { // just write the zip file
                fs = outputFile.OpenWrite();
                fs.Write(stream.GetBuffer(), 0, (int)stream.Position);
                fs.Close();
            }
        }
    }

    /// <summary>
    /// Extracts a ZipOutputStream.
    /// </summary>
    /// <param name="inputStream">The underlying MemoryStream.</param>
    /// <param name="outputPath">The extraction path.</param>
    public static void ExtractZip(MemoryStream inputStream, string outputPath) {
        if(!Directory.Exists(outputPath)) {
            Directory.CreateDirectory(outputPath);
        }
        inputStream.Seek(0, SeekOrigin.Begin);
        using(ZipInputStream stream = new ZipInputStream(inputStream)) {
            ZipEntry entry;
            while((entry = stream.GetNextEntry()) != null) {
                string directoryName = Path.Combine(outputPath, Path.GetDirectoryName(entry.Name));
                string fileName = Path.GetFileName(entry.Name);
                if(directoryName.Length > 0 && !Directory.Exists(directoryName)) { // create the directory if it doesn't exist
                    Directory.CreateDirectory(directoryName);
                }
                if(fileName.Length > 0) {
                    using(FileStream streamWriter = File.Create(Path.Combine(directoryName, fileName))) {
                        int size = 2048;
                        byte[] data = new byte[2048];
                        while(true) {
                            size = stream.Read(data, 0, data.Length);
                            if(size > 0) {
                                streamWriter.Write(data, 0, size);
                            } else {
                                break;
                            }
                        }
                    }
                }
            }
        }
    }

    
}
